﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using QLACQUY.DTO;
using System.Data.OleDb;
using System.Collections;

namespace QLACQUY.DAO
{
    class PhieuGiaoHangDAO:DataAccess
    {
        public ArrayList layDanhSach()
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "select * from PHIEU_GIAO_HANG pgh, KHACH_HANG kh where kh.MaKH = pgh.MaKhachHang";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            ArrayList list = new ArrayList();
            while (dr.Read())
            {
                PhieuGiaoHangCRT crt = new PhieuGiaoHangCRT();
                crt.MaPhieu = (int)dr["MaPhieu"];
                crt.NgayLapPhieu = (System.DateTime)dr["NgayLapPhieu"];
                crt.TongTien = (double)dr["TongTien"];
                crt.MaKH = (int)dr["MaKH"];
                crt.TenKhachHang = (string)dr["TenKH"];
                crt.DiaChi = (string)dr["DiaChiKH"];
                crt.DienThoai = (string)dr["DienThoaiKH"];
                crt.NoCu = (double)dr["pgh.NoCu"];
                crt.TienKhachTra = (double)dr["TienKhachTra"];
                list.Add(crt);
            }
            dr.Close();
            cn.Close();
            return list;
        }
        public int themPhieuGiaoHang(PhieuGiaoHangDTO dto)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "insert into PHIEU_GIAO_HANG(NgayLapPhieu, TongTien, MaKhachHang, MaNVGiaoHang, NoCu, TienKhachTra) values(?,?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@NgayLapPhieu", OleDbType.Date);
            cmd.Parameters["@NgayLapPhieu"].Value = dto.NgayLapPhieu;

            cmd.Parameters.Add("@TongTien", OleDbType.Double);
            cmd.Parameters["@TongTien"].Value = dto.TongTien;

            cmd.Parameters.Add("@MaKhachHang", OleDbType.Integer);
            cmd.Parameters["@MaKhachHang"].Value = dto.MaKH;            

            cmd.Parameters.Add("@MaNVGiaoHang", OleDbType.Integer);
            cmd.Parameters["@MaNVGiaoHang"].Value = dto.MaNVGiaoHang;

            cmd.Parameters.Add("@NoCu", OleDbType.Double);
            cmd.Parameters["@NoCu"].Value = dto.NoCu;

            cmd.Parameters.Add("@TienKhachTra", OleDbType.Double);
            cmd.Parameters["@TienKhachTra"].Value = dto.TienKhachTra;

            try
            {
                cmd.ExecuteNonQuery();
                cmd.CommandText = "select @@IDENTITY";
                return (int)cmd.ExecuteScalar();
            }
            catch (System.Exception ex)
            {
                return 0;
            }            
        }
        public int xoaPhieu(int maphieu)
        {
            OleDbConnection cn = new OleDbConnection();
            cn = this.DataConnection();
            string strSQL = "delete from CHI_TIET_PHIEU_GIAO_HANG where MaPhieuGiaoHang = " + maphieu;
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);

            try
            {
                cmd.ExecuteNonQuery();
                cmd.CommandText = "delete from PHIEU_GIAO_HANG where MaPhieu="+maphieu;
                cmd.ExecuteNonQuery();
                return 1;
            }
            catch (System.Exception ex)
            {
                return 0;
            }         
        }
    }
}
